package db.tables;


//import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import control.ConnectionManage;
import db.parking.beans.ParkingLot;

/**
 * ParkingLotAdmin manages all requests From,To table ParkingLot
 * @author Operations
 *
 */
public class ParkingLotAdmin {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();

	public static ParkingLot[] getAllParkingLot() throws SQLException {
		String sql = "SELECT * FROM parkinglot";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			ParkingLot bean[] = new ParkingLot[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new ParkingLot();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setLotID(rs.getInt("lotNo"));
				bean[rowCount].setCapacity(rs.getInt("capacity"));
				bean[rowCount].setTotalCars(rs.getInt("totalCars"));
				bean[rowCount].setX(rs.getInt("x"));
				bean[rowCount].setX(rs.getInt("y"));
				bean[rowCount].setX(rs.getInt("z"));
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	/** Get ParkingLot  deatils from date base
	 * @param lotId
	 * @return ParkingLot 
	 * @throws SQLException
	 */
	public static ParkingLot getRow(int lotId) throws SQLException {

		String sql = "SELECT * FROM ParkingLot WHERE lotID = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, lotId);
			rs = stmt.executeQuery();

			if (rs.next()) {
				ParkingLot bean = new ParkingLot();
				bean.setLotID(lotId);
				bean.setCapacity(rs.getInt("capacity"));
				bean.setTotalCars(rs.getInt("totalCars"));
				bean.setX(rs.getInt("x"));
				bean.setY(rs.getInt("y"));
				bean.setZ(rs.getInt("z"));

				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	
	/**
	 * Functions helps control number of cars in specific lot
	 * @param lotID
	 * @param newNumOfCars
	 * @return
	 * @throws Exception
	 */
	public static boolean update(int lotID,int newNumOfCars) throws Exception {

		String sql =
				"UPDATE ParkingLot SET " +
				"totalCars = ? " +
				"WHERE lotID = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,newNumOfCars);
			stmt.setInt(2,lotID);
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
	
}
